import easyquotation
import sqlite3
from seafile_api import SeafAPI
from datetime import datetime

now = datetime.now()
time_str = now.strftime("%y%m%d_%H")

# Get all stocks infomation
quotation = easyquotation.use('sina')
all_info = quotation.market_snapshot(prefix=True)
# Create database
connect = sqlite3.connect("stock.db")
cursor = connect.cursor()
cursor.execute("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%s';" % time_str)
result = cursor.fetchone()[0]
if not result:
	cursor.execute('''CREATE TABLE IF NOT EXISTS '%s' 
		(股票代码 VARCHAR(10) PRIMARY KEY NOT NULL,
		名称 VARCHAR(10) NOT NULL,
		竞买价 FLOAT,
		竞卖价 FLOAT,
		现价 FLOAT,
		开盘价 FLOAT,
		昨日收盘价 FLOAT,
		最高价 FLOAT,
		最低价 FLOAT,
		交易股数 INT,
		交易金额);''' % time_str)
	# Write database
	cursor.executemany("INSERT INTO '%s' VALUES \
		(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" % time_str,
		[(key, value['name'], value['buy'], value['sell'], value['now'],
			value['open'], value['close'], value['high'], value['low'],
			value['turnover'], value['volume'])\
		 for key, value in all_info.items()])
# Close database
cursor.close()
connect.commit()
connect.close()

# Delete remote file and upload a new file
api = SeafAPI("http://192.168.210.128:8000", 'a@a.com', '1')
repo_id = api.all_repo_id.get("私人资料库")
upload_link = api.get_upload_link(repo_id)
api.delete_file(repo_id, "/stock.db")
api.upload_file(upload_link, 'stock.db')
